package com.onlineMIS.ORM.DAO.headQ.supplier.finance;

import java.io.ByteArrayInputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.onlineMIS.ORM.DAO.Response;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.AreaDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.BrandDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.CategoryDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.ColorDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.HeadQEntityTagDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.QuarterDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.YearDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.custMgmt.HeadQCustDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.finance.FinanceCategoryImpl;
import com.onlineMIS.ORM.DAO.headQ.inventory.InventoryOrderDAOImpl;
import com.onlineMIS.ORM.DAO.headQ.supplier.purchase.PurchaseOrderDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.supplier.supplierMgmt.HeadQSupplierDaoImpl;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.HeadQTag;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.HeadqEntityTag;
import com.onlineMIS.ORM.entity.headQ.custMgmt.HeadQCust;
import com.onlineMIS.ORM.entity.headQ.finance.ChainAcctFlowReportItem;
import com.onlineMIS.ORM.entity.headQ.finance.FinanceBill;
import com.onlineMIS.ORM.entity.headQ.finance.FinanceCategory;
import com.onlineMIS.ORM.entity.headQ.finance.HeadQAccFlowReportItem;
import com.onlineMIS.ORM.entity.headQ.finance.SupplierAccFlowTemplate;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.FinanceSummaryRptVOEles;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrder;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.FinanceBillSupplier;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.FinanceBillSupplierItem;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.FinanceBillSupplierVO;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.SupplierAcctFlow;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.SupplierAcctFlowReportItem;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.SupplierFinanceSummaryItem;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.SupplierFinanceTrace;
import com.onlineMIS.ORM.entity.headQ.supplier.purchase.PurchaseOrder;
import com.onlineMIS.ORM.entity.headQ.supplier.supplierMgmt.HeadQSupplier;
import com.onlineMIS.ORM.entity.headQ.user.UserInfor;
import com.onlineMIS.action.headQ.supplier.finance.FinanceSupplierActionFormBean;
import com.onlineMIS.action.headQ.supplier.finance.FinanceSupplierActionUIBean;
import com.onlineMIS.common.Common_util;
import com.onlineMIS.common.DateUtils;
import com.onlineMIS.common.ExcelUtil;
import com.onlineMIS.common.loggerLocal;
import com.onlineMIS.filter.SystemParm;
import com.onlineMIS.sorter.HeadQAccFlowReportItemSort;
import com.onlineMIS.sorter.SupplierAcctFlowReportItemSort;


@Service
public class FinanceSupplierService {
    @Autowired
	private FinanceCategoryImpl financeCategoryImpl;
    @Autowired
    private HeadQSupplierDaoImpl headQSupplierDaoImpl;
    @Autowired
    private SupplierAcctFlowDaoImpl supplierAcctFlowDaoImpl;
    @Autowired
    private FinanceBillSupplierDaoImpl financeBillSupplierDaoImpl;
    @Autowired
    private SupplierFinanceTraceImpl financeTraceImpl;
    @Autowired
    private PurchaseOrderDaoImpl purchaseOrderDaoImpl;
    @Autowired
    private SupplierFinanceTraceImpl supplierFinanceTraceImpl;
    @Autowired
    private FinanceCategoryFlowDaoImpl financeCategoryFlowDaoImpl;
	@Autowired
	private com.onlineMIS.ORM.DAO.headQ.barCodeGentor.ProductDaoImpl productDaoImpl;
	@Autowired
	private com.onlineMIS.ORM.DAO.headQ.barCodeGentor.ProductBarcodeDaoImpl productBarcodeDaoImpl;
	@Autowired
	private HeadQCustDaoImpl headQCustDaoImpl;
	@Autowired
	private HeadQEntityTagDaoImpl headQEntityTagDaoImpl;
	/**
	 * to prepare the bean when create the finance bill
	 * @param formBean
	 * @param uiBean
	 */
	public Response prepareCreateBill(FinanceSupplierActionFormBean formBean, FinanceSupplierActionUIBean uiBean){		
		Response response = initializeFianceBill(formBean);
		return response;
	}
	
	/**
	 * initialize the finance bill
	 */
	private Response initializeFianceBill(FinanceSupplierActionFormBean formBean){
		Response response = new Response();
		FinanceBillSupplier financeBill = formBean.getOrder();
		List<FinanceBillSupplierItem> billItems = new ArrayList<FinanceBillSupplierItem>();
		List<FinanceCategory> allCategories = new ArrayList<FinanceCategory>();
		//供应商财务单据-进入初始页面时，默认为付款单
		if (financeBill.getType() == Common_util.ALL_RECORD_NEW) {
			financeBill.setType(FinanceBill.FINANCE_PAID_HQ);			
		}		
		int belongTo2 = 0;
		if (financeBill.getSupplier().getId() != Common_util.ALL_RECORD_NEW) {
			HeadqEntityTag  qxEntityTag = headQEntityTagDaoImpl.selectEntityTag(financeBill.getSupplier().getId(), HeadQTag.HEADQ_QX_SUPPLIER_TAG_ID);
			if (qxEntityTag !=null) {
				belongTo2 = FinanceCategory.BELONG_TO_QX;
			}
			HeadqEntityTag xlcEntityTag = headQEntityTagDaoImpl.selectEntityTag(financeBill.getSupplier().getId(), HeadQTag.HEADQ_XLC_SUPPLIER_TAG_ID);
			if (xlcEntityTag !=null) {
				belongTo2 = FinanceCategory.BELONG_TO_XLC;
			}
			if(qxEntityTag ==null && xlcEntityTag == null) {
				response.setFail("当前供应商暂未选择归属，请选择供应商归属后再创建财务单据");
			}else {
				//1.付款单
				if (financeBill.getType() == FinanceBill.FINANCE_PAID_HQ) {
					allCategories = financeCategoryImpl.selectFcBelongTo(FinanceCategory.BELONG_TO_HQ,belongTo2);
				//2.收款单
				}else if (financeBill.getType() == FinanceBill.FINANCE_INCOME_HQ) {
					allCategories = financeCategoryImpl.selectFcBelongTo(FinanceCategory.BELONG_TO_HQ,belongTo2);
				//3.应付增加，应付减少	（应付账户）
				}else if (financeBill.getType() == FinanceBill.FINANCE_DECREASE_HQ || financeBill.getType() == FinanceBill.FINANCE_INCREASE_HQ) {
					allCategories.add(financeCategoryImpl.selectByType(FinanceCategory.INCREASE_DECREASE_ACCT_SUPPLIER_TYPE,belongTo2));
				}
			}
			HeadQSupplier headQSupplier = headQSupplierDaoImpl.get(financeBill.getSupplier().getId(), true);
			if (headQSupplier != null) {
				formBean.getOrder().setSupplier(headQSupplier);
			}
		}

		if (allCategories != null){
			for (int i = 0; i < allCategories.size(); i++){
				FinanceBillSupplierItem billItem = new FinanceBillSupplierItem();
				billItem.setFinanceCategory(allCategories.get(i));
				billItems.add(billItem);
			}
			financeBill.setFinanceBillItemList(billItems);
		}
		
		financeBill.setBillDate(new java.sql.Date(new Date().getTime()));
		return response;
	}
	
	/**
	 * 获取连锁店当前欠款
	 * @param chainId
	 * @return
	 */
	public Response getSupplierCurrentFinance(int supplierId) {
		Response response = new Response();
		
		HeadQSupplier supplier = headQSupplierDaoImpl.get(supplierId, true);
		
		//1. Get the current finance
		double initialAcct = supplier.getInitialAcctBalance();
		double currentFinance = initialAcct + supplierAcctFlowDaoImpl.getAccumulateAcctFlow(supplierId);
		currentFinance = Common_util.roundDouble(currentFinance, 2);

		
		response.setReturnCode(Response.SUCCESS);
		
		Map<String, Double> financeDataMap = new HashMap<String, Double>();
		financeDataMap.put("cf", currentFinance);
		
		response.setReturnValue(financeDataMap);
		
		return response;
	}

	/**
	 * 保存到 草稿
	 * @param order
	 * @return
	 * @throws ParseException 
	 */
	public Response saveFBToDraft(FinanceBillSupplier financeBill, UserInfor user) throws ParseException {

		Response response = new Response();
		int billId = financeBill.getId();
		
		//Validate the bill
        validateFB(financeBill, response);
        if (!response.isSuccess())
        	return response;
        
        
		FinanceBillSupplier originalBill = financeBillSupplierDaoImpl.get(billId, true);
		
		if (originalBill == null || originalBill.getStatus() == FinanceBill.STATUS_DRAFT){
			financeBillSupplierDaoImpl.evict(originalBill);
			
			financeBill.setStatus(FinanceBill.STATUS_DRAFT);
			
			if (originalBill != null && originalBill.getStatus() == FinanceBill.STATUS_DRAFT) {
				financeBill.setCreateDate(originalBill.getCreateDate());
				financeBill.setUpdateDate(new Date());
			}else {
				financeBill.setCreateDate(new Date());
				financeBill.setUpdateDate(new Date());
			}
			financeBill.setCreatorHq(user);
			
			financeBill.putListToSet();
			
			financeBillSupplierDaoImpl.saveOrUpdate(financeBill, true);
			
			response.setQuickValue(Response.SUCCESS, "单据成功保存草稿");
		} else {
			response.setReturnCode(Response.ERROR);
			response.setMessage("保存草稿出现错误  : 已经过账或者红冲的单据不能重复保存草稿");
		}
		
		return response;
	}
	
	/**
	 * 单据过账供应商财务单据
	 * @param financeBill
	 * @return
	 * @throws ParseException 
	 */
	public Response postFB(FinanceBillSupplier financeBill, UserInfor user) throws ParseException{
		Response response = new Response();
		
		//Validate the bill
        validateFB(financeBill, response);
        if (!response.isSuccess())
        	return response;
		
		int billId = financeBill.getId();
		FinanceBillSupplier originalBill = financeBillSupplierDaoImpl.get(billId, true);
		
		if (originalBill == null || originalBill.getStatus() == FinanceBill.STATUS_DRAFT){
			financeBillSupplierDaoImpl.evict(originalBill);

			//1. change the bill status
			financeBill.setStatus(FinanceBill.STATUS_COMPLETE);
			if (originalBill != null && originalBill.getStatus() == FinanceBill.STATUS_DRAFT) {
				financeBill.setCreateDate(originalBill.getCreateDate());
				financeBill.setUpdateDate(new Date());
			}else {
				financeBill.setCreateDate(new Date());
				financeBill.setUpdateDate(new Date());
			}
			
			
			financeBill.setCreatorHq(user);
			financeBill.putListToSet();
			financeBillSupplierDaoImpl.saveOrUpdate(financeBill, true);
			
			//2.0 updat the finance trace
			updateFinanceTrace(financeBill, false);
			
			//3.0 update the acct flow
			updateSupplierAcctFlow(financeBill, false);
			
			//4.0 更新finance category flow
			financeCategoryFlowDaoImpl.updateFinanceCategoryFlow(financeBill,null,false,false);
			
			
			HeadQSupplier hSupplier = headQSupplierDaoImpl.get(financeBill.getSupplier().getId(), true);
			
			//1. 获取当前客户的账目
			double currentFinance = 0;
			Response response2 = getSupplierCurrentFinance(financeBill.getSupplier().getId());
			if (response2.isSuccess())
				currentFinance = ((Map<String, Double>)response2.getReturnValue()).get("cf");
			else {
				response.setFail("更新客户账户错误");
				return response;
			}
			
			hSupplier.setCurrentAcctBalance(currentFinance);
			headQSupplierDaoImpl.update(hSupplier, true);
			
			
			response.setReturnCode(Response.SUCCESS);
			response.setMessage("单据成功过账");
		} else {
			response.setReturnCode(Response.ERROR);
			response.setMessage("单据过账出现错误");
		}
		
		return response;
	}
	
	/**
	 * 验证财务单据
	 * @param financeBill
	 * @param response
	 * @throws ParseException 
	 */
	private void validateFB(FinanceBillSupplier financeBill, Response response) throws ParseException {
		if (financeBill.getSupplier() == null || financeBill.getSupplier().getId() ==0){
			response.setFail("供应商是必填项");
			return;
		}
		for (FinanceBillSupplierItem billItem: financeBill.getFinanceBillItemList()){
			if (billItem.getTotal() < 0){
				response.setFail("金额必须是大于或等于零的数字");
				return;
			}   
		}
		
		//判断流水时间是否超过规定的清账时间之前
		int days = DateUtils.diffDays(DateUtils.parseDateToStr(SystemParm.getAccountFlowDate()), DateUtils.parseDateToStr(financeBill.getBillDate()));
		if (days <0) {
			response.setFail("无法创建：" + Common_util.dateFormat.format(SystemParm.getAccountFlowDate()) + "之前的单据");
			return ;
		}
		
	}

	/**
	 * to update the finance trace
	 * @param bill
	 * @param isCancel
	 */
	private void updateFinanceTrace(FinanceBillSupplier bill, boolean isCancel){
		int supplierId = bill.getSupplier().getId();
		String billId = String.valueOf(bill.getId());
		int offset = isCancel ? -1 : 1;
		billId= isCancel ? "C" + billId : billId;
		
		int billType = bill.getType();
		
		//if it is 总部的付款单需要*-1
		if (billType == FinanceBillSupplier.FINANCE_PAID_HQ || billType == FinanceBillSupplier.FINANCE_DECREASE_HQ)
			offset *= -1;
		
		double totalAmt = 0;
		//1. to update the bill items
		for (FinanceBillSupplierItem billItem : bill.getFinanceBillItemSet()){
			double amount = billItem.getTotal();
			
			if (amount == 0)
				continue;
			
			int categoryId = billItem.getFinanceCategory().getId();
			int type = financeCategoryImpl.get(categoryId, true).getType();

			SupplierFinanceTrace financeTrace = supplierFinanceTraceImpl.getByPK(supplierId, type, billId);
			if (financeTrace == null)
				financeTrace = new SupplierFinanceTrace(supplierId, type, billId, amount * offset, bill.getBillDate());
			else {
				double amt = financeTrace.getAmount();
				amt += amount * offset;
				financeTrace.setAmount(amt);
			}
			financeTraceImpl.saveOrUpdate(financeTrace, false);
		}

		//2. if it is prepaid bill, need insert a prepaid amount
		if (billType ==FinanceBillSupplier.FINANCE_PREPAID_HQ){
			SupplierFinanceTrace financeTrace = new SupplierFinanceTrace(supplierId, FinanceCategory.PREPAY_ACCT_SUPPLIER_TYPE, billId, totalAmt * offset , bill.getBillDate());
			financeTraceImpl.saveOrUpdate(financeTrace, false);
		}
	}
	
	private void updateSupplierAcctFlow(FinanceBillSupplier bill, boolean isCancel){
		int supplierId = bill.getSupplier().getId();
		
		HeadQSupplier supplier = headQSupplierDaoImpl.get(supplierId, true);
		
		if (supplier !=  null){
			int billType = bill.getType();
			int offset = 1;
			
			//1. check the finance bill type
			if (billType == FinanceBillSupplier.FINANCE_PAID_HQ || billType == FinanceBillSupplier.FINANCE_DECREASE_HQ){
				offset *= -1;
			} else if (billType == FinanceBillSupplier.FINANCE_INCOME_HQ || billType == FinanceBillSupplier.FINANCE_INCREASE_HQ){
				offset *= 1;
			} else {
				offset *= 1000;
			}
			
			//2. check is cancel
			if (isCancel)
				offset *= -1;
			
			//3. calculate the pre-acct and postAcctAmt
    		double initialAcctAmt = supplier.getInitialAcctBalance();
    		double acctAmtFlow = supplierAcctFlowDaoImpl.getAccumulateAcctFlow(supplierId);
    		double preAcctAmt = Common_util.getDecimalDouble(initialAcctAmt + acctAmtFlow);
    		
    		//4. 设计折扣
    		double invoiceTotal = bill.getInvoiceTotal();
			if (billType == FinanceBillSupplier.FINANCE_PAID_HQ) {
				invoiceTotal += bill.getInvoiceDiscount();
			} else if ( billType == FinanceBillSupplier.FINANCE_INCOME_HQ){
				invoiceTotal -= bill.getInvoiceDiscount();
			}
			double netAmt = offset * invoiceTotal;
			double postAcctAmt = Common_util.getDecimalDouble(preAcctAmt + netAmt);
    		
    		//5.update the finance bill's pre and post 
    		if (!isCancel){
    		   bill.setPreAcctAmt(preAcctAmt);
    		   bill.setPostAcctAmt(postAcctAmt);
    		   financeBillSupplierDaoImpl.update(bill, true);
    		}
    		
    		//6。更新supplier信息
    		supplier.setCurrentAcctBalance(postAcctAmt);
		    headQSupplierDaoImpl.update(supplier, true);
    		
    		//7. 更新acct folow
			SupplierAcctFlow supplierAcctFlow = new SupplierAcctFlow(supplierId, netAmt, "F," + bill.getId() + "," + isCancel, bill.getBillDate());

			supplierAcctFlowDaoImpl.save(supplierAcctFlow, true);
		}
	}
	
	/**
	 * function to search the finance bills based on the search criteria
	 * @param formBean
	 * @return
	 */
	public Response searchFHQBills(FinanceSupplierActionFormBean formBean,int page, int rowPerPage, String sort, String order) {
		Response response = new Response();
		boolean cached = false;
		Map data = new HashMap<String, Object>();
		List<FinanceBillSupplierVO> list = new ArrayList<FinanceBillSupplierVO>();
		
		int totalRecord = 0;

		DetachedCriteria criteria = buildSearchFHQBills(formBean);
		criteria.setProjection(Projections.rowCount());
		totalRecord = Common_util.getProjectionSingleValue(financeBillSupplierDaoImpl.getByCriteriaProjection(criteria, false));

		
		DetachedCriteria criteria2 = buildSearchFHQBills(formBean);
		criteria2.add(Restrictions.between("billDate", Common_util.formStartDate(formBean.getSearchStartTime()), Common_util.formEndDate(formBean.getSearchEndTime())));
		criteria2.addOrder(Order.desc("billDate"));
			
		List<FinanceBillSupplier> fList =  financeBillSupplierDaoImpl.getByCritera(criteria2, Common_util.getFirstRecord(page, rowPerPage), rowPerPage, cached);
		
		if (fList.size() != 0) {
			for (FinanceBillSupplier financeBillSupplier : fList) {
				FinanceBillSupplierVO financeBillSupplierVO = new FinanceBillSupplierVO(financeBillSupplier);
				list.add(financeBillSupplierVO);
			}
		}		

		List<Object> result = new ArrayList<Object>();
		
		data.put("rows", list);
		data.put("total", totalRecord);
		
		response.setReturnValue(data);

		return response;
		
	}
	
	private DetachedCriteria buildSearchFHQBills(FinanceSupplierActionFormBean formBean){
        DetachedCriteria criteria = DetachedCriteria.forClass(FinanceBillSupplier.class);
        FinanceBillSupplier financeBill = formBean.getOrder();
		
		//1. get the status
		int status = financeBill.getStatus();
		if (status != Common_util.ALL_RECORD_NEW)
			criteria.add(Restrictions.eq("status", status));
		else 
			criteria.add(Restrictions.ne("status", FinanceBill.STATUS_DELETED));
		
		//2. get the category
		int type = financeBill.getType();
		if (type != Common_util.ALL_RECORD_NEW)
			criteria.add(Restrictions.eq("type", type));
		
		
		//2. get the chain
		int supplierId = financeBill.getSupplier().getId();
		if (supplierId != Common_util.ALL_RECORD_NEW)
			criteria.add(Restrictions.eq("supplier.id", supplierId));
		
		String comment = financeBill.getComment();

		if (StringUtils.isNotBlank(comment)){
			criteria.add(Restrictions.like("comment", comment, MatchMode.ANYWHERE));
		}
				
		//4. set the date
		Date startDate = Common_util.formStartDate(formBean.getSearchStartTime());
		Date endDate = Common_util.formEndDate(formBean.getSearchEndTime());
		criteria.add(Restrictions.between("billDate", startDate, endDate));
		
		return criteria;
	}

	/**
	 * 根据financeBill id获取financeBill 
	 * @param id
	 * @return
	 */
	@Transactional
	public Response getFinanceBillById(int id) {
		Response response = new Response();
		
		try {
		
			FinanceBillSupplier financeBill = financeBillSupplierDaoImpl.get(id, true);
			financeBillSupplierDaoImpl.initialize(financeBill);
			financeBill.putSetToList();
			
			response.setReturnValue(financeBill);
		} catch (Exception e){
			response.setFail("获取单据错误 : " + e.getMessage());
		}
		
		return response;
	}

	public void prepareEditBill(FinanceSupplierActionFormBean formBean,
			HeadQSupplier supplier) {
		formBean.getOrder().setSupplier(supplier);
		
	}
	
	@Transactional
	public Response cancelFB(FinanceBillSupplier order, UserInfor user) throws ParseException{
		Response response = new Response();
		int billId = order.getId();
		FinanceBillSupplier originalBill = financeBillSupplierDaoImpl.get(billId, true);
		
		if (originalBill == null || originalBill.getStatus() != FinanceBill.STATUS_COMPLETE){
			response.setReturnCode(Response.ERROR);
			response.setMessage("单据不存在或者尚未过账无法红冲");
		} else {
			//判断单据时间是否超过规定的清账时间之前
			int days = DateUtils.diffDays(DateUtils.parseDateToStr(SystemParm.getAccountFlowDate()), DateUtils.parseDateToStr(originalBill.getBillDate()));
			if (days <0) {
				response.setReturnCode(Response.ERROR);
				response.setMessage("无法红冲：" + Common_util.dateFormat.format(SystemParm.getAccountFlowDate()) + "之前的单据");
			}else {
			//1.0 change the bill status 
			originalBill.setStatus(FinanceBill.STATUS_CANCEL);
			originalBill.setCreatorHq(user);
			
			//originalBill.setCreateDate(new Date());	
			financeBillSupplierDaoImpl.update(originalBill, true);

			//2.0 updat the finance trace
			updateFinanceTrace(originalBill, true);

			//3.0 update the acct flow
			updateSupplierAcctFlow(originalBill, true);
			
			//4.0 更新finance category flow  红冲
			financeCategoryFlowDaoImpl.updateFinanceCategoryFlow(originalBill,null, true,false);

			response.setReturnCode(Response.SUCCESS);
			}
		}
		
		return response;
	}

	public Response deleteFB(FinanceBillSupplier order, UserInfor user) {
		Response response = new Response();
		int billId = order.getId();
		FinanceBillSupplier originalBill = financeBillSupplierDaoImpl.get(billId, true);
		
		if (originalBill != null && originalBill.getStatus() == FinanceBill.STATUS_DRAFT){
			originalBill.setStatus(FinanceBill.STATUS_DELETED);
			originalBill.setCreateDate(new Date());
			originalBill.setCreatorHq(user);
		
			financeBillSupplierDaoImpl.saveOrUpdate(originalBill, true);
		    
		    response.setReturnCode(Response.SUCCESS);
		} else {
			response.setReturnCode(Response.ERROR);
			response.setMessage("删除单据出错");
		}
		
		return response;
	}
	
	/**
	 * 更新财务单据备注
	 * @param order
	 * @param loginUserInfor
	 * @return
	 */
	@Transactional
	public Response updateFinanceBillComment(FinanceBillSupplier financeBill, UserInfor loginUserInfor) {
		Response response = new Response();
		int billId = financeBill.getId();
		FinanceBillSupplier originalBill = financeBillSupplierDaoImpl.get(billId, true);
		
		if (originalBill != null){
			originalBill.setComment(financeBill.getComment());
			
			//1. change the bill status
			financeBillSupplierDaoImpl.saveOrUpdate(originalBill, true);
			
			response.setReturnCode(Response.SUCCESS);
			response.setMessage("单据备注 成功修改");
		} else {
			response.setReturnCode(Response.ERROR);
			response.setMessage("无法找到当前单据 : " + billId);
		}
		
		return response;
	}

	
	/**
	 * 查询供应商财务往来账目
	 * @param startDate
	 * @param endDate
	 * @param supplierId
	 * @param comment
	 * @param tagId
	 * @return
	 */
	public Response searchAcctFlow(Date startDate, Date endDate, int supplierId, String comment,int tagId) {
		Response response = new Response();
		List<Integer> supplierIds = new ArrayList<Integer>();
		if (tagId != Common_util.ALL_RECORD) {
			supplierIds = headQEntityTagDaoImpl.selectIdsByTagId(tagId);
		}
		 
		List<SupplierAcctFlowReportItem> rptItems = new ArrayList<SupplierAcctFlowReportItem>();

		/**
		 * 1. 搜索所有采购单据
		 */
		List<PurchaseOrder> purchaseOrders = new ArrayList<PurchaseOrder>();
		DetachedCriteria criteria = DetachedCriteria.forClass(PurchaseOrder.class, "order");
		criteria.add(Restrictions.eq("order.status", PurchaseOrder.STATUS_COMPLETE));
		if (supplierId != Common_util.ALL_RECORD && supplierId != Common_util.ALL_RECORD_NEW) {
			criteria.add(Restrictions.eq("order.supplier.id", supplierId));
		}else {
			if (supplierIds.size() !=0) {
				criteria.add(Restrictions.in("order.supplier.id", supplierIds));
			}
		}

		if (!comment.trim().equals("")) {
			criteria.add(Restrictions.like("order.comment", comment, MatchMode.ANYWHERE));
		}

		startDate = Common_util.formStartDate(startDate);
		endDate = Common_util.formEndDate(endDate);
		criteria.add(Restrictions.between("order.lastUpdateTime", startDate, endDate));
		purchaseOrders = purchaseOrderDaoImpl.getByCritera(criteria, true);

		/**
		 * 2. 搜索所有财物单据
		 */
		DetachedCriteria criteria2 = DetachedCriteria.forClass(FinanceBillSupplier.class);
		criteria2.add(Restrictions.eq("status", FinanceBill.STATUS_COMPLETE));
		if (supplierId != Common_util.ALL_RECORD && supplierId != Common_util.ALL_RECORD_NEW) {
			criteria2.add(Restrictions.eq("supplier.id", supplierId));
		}else {
			if (supplierIds.size() !=0) {
				criteria2.add(Restrictions.in("supplier.id", supplierIds));
			}
		}
			
		if (!comment.trim().equals("")) {
			criteria2.add(Restrictions.like("comment", comment, MatchMode.ANYWHERE));
		}

		criteria2.add(Restrictions.between("billDate", startDate, endDate));
		List<FinanceBillSupplier> financeBills = financeBillSupplierDaoImpl.getByCritera(criteria2, true);

		constructSupplierAcctFlowRptItems(purchaseOrders, financeBills, rptItems, supplierId,supplierIds, startDate);

		Map data = new HashMap<String, List>();
		data.put("rows", rptItems);
		response.setReturnValue(data);
		response.setReturnCode(Response.SUCCESS);

		return response;
	}

	private void constructSupplierAcctFlowRptItems(List<PurchaseOrder> purchaseOrders,
			List<FinanceBillSupplier> financeBills, List<SupplierAcctFlowReportItem> rptItems, int supplierId,List<Integer> supplierIds,
			Date startDate) {
		//1. 货品采购单
		if (purchaseOrders != null && purchaseOrders.size() >0){
			for (PurchaseOrder order : purchaseOrders){
				HeadQSupplier supplier = order.getSupplier();
				String orderType = order.getTypeS();
				
				String acctFlowType ="";
				if (order.getType() == PurchaseOrder.TYPE_PURCHASE)
					acctFlowType = SupplierAcctFlowReportItem.ACCT_FLOW_TYPE_INCREASE;
				else if (order.getType() == PurchaseOrder.TYPE_RETURN)
					acctFlowType = SupplierAcctFlowReportItem.ACCT_FLOW_TYPE_DECREASE;
				
				SupplierAcctFlowReportItem acctFlowItem = new SupplierAcctFlowReportItem(supplier, order.getLastUpdateTime(), orderType, SupplierAcctFlowReportItem.ITEM_TYPE_PURCHASE,acctFlowType, order.getTotalQuantity(), order.getTotalRecCost(),order.getTotalRecCost() - order.getTotalDiscount(), order.getId(),order.getComment(), 0,0);
				rptItems.add(acctFlowItem);
			}
		}
		
		//2. 财务单据
		if (financeBills != null && financeBills.size() > 0){
			for (FinanceBillSupplier bill : financeBills){
				HeadQSupplier supplier = bill.getSupplier();
				String billTypeName = bill.getTypeHQS();
				
				int billType = bill.getType();
				String acctFlowType = "";
				double invoiceTotal = bill.getInvoiceTotal();
				double amtFlow = invoiceTotal;
				if (billType == FinanceBill.FINANCE_DECREASE_HQ || billType == FinanceBill.FINANCE_PAID_HQ) {
					acctFlowType = SupplierAcctFlowReportItem.ACCT_FLOW_TYPE_DECREASE;
					amtFlow += bill.getInvoiceDiscount();
				} else if (billType == FinanceBill.FINANCE_INCREASE_HQ || billType == FinanceBill.FINANCE_INCOME_HQ){
					acctFlowType = SupplierAcctFlowReportItem.ACCT_FLOW_TYPE_INCREASE;
					amtFlow -= bill.getInvoiceDiscount();
				}
				
				SupplierAcctFlowReportItem acctFlowItem = new SupplierAcctFlowReportItem(supplier, bill.getBillDate(), billTypeName, SupplierAcctFlowReportItem.ITEM_TYPE_FINANCE, acctFlowType, 0, invoiceTotal, amtFlow, bill.getId(), bill.getComment(), 0,0);
				rptItems.add(acctFlowItem);
			}
		}
		
		//3. 排序一下
		Collections.sort(rptItems, new SupplierAcctFlowReportItemSort());
		
		//4. 获取前面的累计欠款
		Date firstDate = null;
		double acctBefore  = 0;
		if (rptItems.size() > 0){
			firstDate = rptItems.get(0).getDate();
		} else 
			firstDate = startDate;
			
		
		if (firstDate != null && supplierIds.size() !=0) {
		    acctBefore = supplierAcctFlowDaoImpl.getAccumulateAcctFlowBefore(supplierIds, firstDate);
		    double initialAcctBalance = headQSupplierDaoImpl.selectInitialAcctByIds(supplierIds);
		    acctBefore += initialAcctBalance;
		}else {
			acctBefore = supplierAcctFlowDaoImpl.getAccumulateAcctFlowBefore(supplierId, firstDate);
			HeadQSupplier supplier = headQSupplierDaoImpl.get(supplierId, true);
			if (supplier != null)
			     acctBefore += supplier.getInitialAcctBalance();
		}
		if (supplierId == Common_util.ALL_RECORD_NEW && supplierIds.size() == 0) {
			acctBefore = supplierAcctFlowDaoImpl.getBeforeDateSumAcctAmt(Common_util.formStartDate(firstDate));
		}
		//5. 准备数据
		double acctAfter = acctBefore;
		for (SupplierAcctFlowReportItem rptItem: rptItems){
			double thisAmt = rptItem.getAmtFlow();
			String acctFlowType = rptItem.getAcctFlowType();
			
			if (acctFlowType.equalsIgnoreCase(ChainAcctFlowReportItem.ACCT_FLOW_TYPE_INCREASE)){
				acctAfter += thisAmt;
				rptItem.setAcctIncrease(thisAmt);
				rptItem.setPostAcct(acctAfter);
			} else if (acctFlowType.equalsIgnoreCase(ChainAcctFlowReportItem.ACCT_FLOW_TYPE_DECREASE)){
				acctAfter = acctAfter - thisAmt;
				rptItem.setAcctDecrease(thisAmt);
				rptItem.setPostAcct(acctAfter);
			} else {
				rptItem.setPostAcct(acctAfter);
			}

		}
		
		//6.0 设置首
		SupplierAcctFlowReportItem firstItem = new SupplierAcctFlowReportItem();
		firstItem.setDate(startDate);
		firstItem.setPostAcct(acctBefore);
		rptItems.add(0, firstItem);
		
	}
	
	public HashSet<Integer> getFinanceBillSupplierIDs(int supplierId,  int type,Date startDate, Date endDate) {
		HashSet<Integer> set = new HashSet<Integer>();
		 //1. 查询数据
	    DetachedCriteria criteria = DetachedCriteria.forClass(FinanceBillSupplier.class);
	    criteria.add(Restrictions.eq("status", FinanceBillSupplier.STATUS_COMPLETE));
	
		if (supplierId != Common_util.ALL_RECORD_NEW) {
		   criteria.add(Restrictions.eq("supplier.id", supplierId));
		}
		Date startDate1 = Common_util.formStartDate(new Date(startDate.getTime()));
		Date endDate1 = Common_util.formEndDate(new Date(endDate.getTime()));
		criteria.add(Restrictions.eq("type", type));
		criteria.add(Restrictions.between("billDate", startDate1, endDate1));
		
		List<FinanceBillSupplier> billSuppliers = financeBillSupplierDaoImpl.getByCritera(criteria, true);
		for (FinanceBillSupplier financeBillSupplier : billSuppliers) {
			set.add(financeBillSupplier.getId());
		}
		return set;
	}

	/**
	 * 
	 * @param id
	 * @param searchStartTime
	 * @param searchEndTime
	 * @return
	 */
	@Transactional
	public Response generateFinanceReport(int supplierId,Date startDate, Date endDate) {
		Response response = new Response();
	    List<FinanceSummaryRptVOEles> footers = new ArrayList<FinanceSummaryRptVOEles>();
	    List<SupplierFinanceSummaryItem> list = new ArrayList<SupplierFinanceSummaryItem>();
	    
	    if (supplierId == 0) {
	    	Set<Integer> qSuppliers = headQSupplierDaoImpl.getAllSupplierIds();
			for (Integer supInteger : qSuppliers) {
				list.addAll(crtateSFsummaryItem(supInteger, startDate, endDate));
			}
		}else {
			list = crtateSFsummaryItem(supplierId, startDate, endDate);
		}


		//Collections.sort(financeSummaryRptVOEles, new FinanceSummaryRptVOElesSorter());

		Map data = new HashMap<String, List>();
		data.put("rows", list);
		data.put("footer", footers);

		response.setReturnValue(data);
		response.setReturnCode(Response.SUCCESS);
		
		
		return response;
	}

	public String getTotal(int fCategoryId,Set<Integer> set) {
			if (set.size()>0) {
			DetachedCriteria fbCriteria = DetachedCriteria.forClass(FinanceBillSupplierItem.class);
			fbCriteria.setProjection(Projections.sum("total"));
			fbCriteria.add(Restrictions.eq("financeCategory.id", fCategoryId));
			fbCriteria.add(Restrictions.in("financeBillSupplier.id", set));
			List<Object> amt =  financeBillSupplierDaoImpl.getByCriteriaProjection(fbCriteria,true);
			return String.valueOf(amt.get(0));
		}else {
			return "0";
		}

	}
	
	
	public List<SupplierFinanceSummaryItem>  crtateSFsummaryItem(int supplierId,Date startDate, Date endDate) {
				//付款
				Set<Integer> pay = getFinanceBillSupplierIDs(supplierId,FinanceBillSupplier.FINANCE_PAID_HQ,startDate,endDate);
				//收款
				Set<Integer> receipt = getFinanceBillSupplierIDs(supplierId,FinanceBillSupplier.FINANCE_INCOME_HQ,startDate,endDate);
				// 供应商
				HeadQSupplier hSupplier = headQSupplierDaoImpl.get(supplierId, true);
				// 银行卡
				DetachedCriteria fCriteria = DetachedCriteria.forClass(FinanceCategory.class);
				fCriteria.add(Restrictions.eq("type", 0));
				fCriteria.add(Restrictions.eq("status", 0));
				List<FinanceCategory>  fc = financeCategoryImpl.getByCritera(fCriteria, true);
				
				List<SupplierFinanceSummaryItem> sfItem =new ArrayList<SupplierFinanceSummaryItem>();
				
				for (FinanceCategory fCategory : fc) {
					String payMoney = getTotal(fCategory.getId(), pay);
					String recepitMoney = getTotal(fCategory.getId(), receipt);
					// 获取卡金额
					SupplierFinanceSummaryItem sReportItem = new SupplierFinanceSummaryItem();
					sReportItem.setSupplierName(hSupplier.getName());
					sReportItem.setItemName(fCategory.getItemName());
					sReportItem.setPayValue(getTotal(fCategory.getId(), pay));
					sReportItem.setReceiptValue(getTotal(fCategory.getId(), receipt));

					if (payMoney.equals(FinanceCategory.ZERO_MONEY) && recepitMoney.equals(FinanceCategory.ZERO_MONEY)) {
						continue;
					}else {
						sfItem.add(sReportItem);
					}
				}
				return sfItem;
	}
	
	
	public Response generateSupplierAcctFlowHQExcel(String path, FinanceSupplierActionFormBean formBean) {
		Response response = new Response();
		try {
			boolean cached = false;
			Date startDate = formBean.getSearchStartTime();
			loggerLocal.info(startDate.toString());
			Date endDate = formBean.getSearchEndTime();
			loggerLocal.info(endDate.toString());
			int supplierId = formBean.getOrder().getSupplier().getId();
			// 查询符合条件的数据
			List<SupplierAcctFlowReportItem> list = searchAcctFlowByExcleDownload(startDate, endDate, supplierId,formBean.getTagId(), false);
			SupplierAccFlowTemplate reportTemplate = new SupplierAccFlowTemplate(formBean.getSearchStartTime(),
					formBean.getSearchEndTime(), formBean.getOrder().getSupplier().getName(), path, list);
			HSSFWorkbook wb = reportTemplate.process();
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);

			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (Exception e) {
			loggerLocal.error(e);
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}
	
	/**
	 * 
	 * @param startDate
	 * @param endDate
	 * @param chainId
	 * @return
	 */
	public List<SupplierAcctFlowReportItem> searchAcctFlowByExcleDownload(Date startDate, Date endDate, int supplierId,int tagId,
			boolean isChain) {
		List<Integer> supplierIds = new ArrayList<Integer>();
		if (tagId != Common_util.ALL_RECORD) {
			supplierIds = headQEntityTagDaoImpl.selectIdsByTagId(tagId);
		}
		 List<SupplierAcctFlowReportItem> rptItems = new ArrayList<SupplierAcctFlowReportItem>();
		/**
		 * 1. 搜索所有采购单据
		 */
		List<PurchaseOrder> purchaseOrders = new ArrayList<PurchaseOrder>();
		DetachedCriteria criteria = DetachedCriteria.forClass(PurchaseOrder.class, "order");
		criteria.add(Restrictions.eq("order.status", PurchaseOrder.STATUS_COMPLETE));
		if (supplierId != Common_util.ALL_RECORD && supplierId != Common_util.ALL_RECORD_NEW) {
			criteria.add(Restrictions.eq("order.supplier.id", supplierId));
		}else {
			if (supplierIds.size() !=0) {
				criteria.add(Restrictions.in("order.supplier.id", supplierIds));
			}
		}

		startDate = Common_util.formStartDate(startDate);
		endDate = Common_util.formEndDate(endDate);
		criteria.add(Restrictions.between("order.lastUpdateTime", startDate, endDate));
		// criteria.addOrder(Order.asc("order.order_StartTime"));
		purchaseOrders = purchaseOrderDaoImpl.getByCritera(criteria, true);
		
		

		/**
		 * 2. 搜索所有财物单据
		 */
		DetachedCriteria criteria2 = DetachedCriteria.forClass(FinanceBillSupplier.class);
		criteria2.add(Restrictions.eq("status", FinanceBill.STATUS_COMPLETE));
		if (supplierId != Common_util.ALL_RECORD && supplierId != Common_util.ALL_RECORD_NEW) {
			criteria2.add(Restrictions.eq("supplier.id", supplierId));
		}else {
			if (supplierIds.size() !=0) {
				criteria2.add(Restrictions.in("supplier.id", supplierIds));
			}
		}
		
		criteria2.add(Restrictions.between("billDate", startDate, endDate));
		List<FinanceBillSupplier> financeBills = financeBillSupplierDaoImpl.getByCritera(criteria2, true);
		
		constructSupplierAcctFlowRptItems(purchaseOrders, financeBills, rptItems, supplierId,supplierIds, startDate);
		
		return rptItems;
	}
	
	
	/**
	 * 构建账户流水
	 * @param purchaseOrders
	 * @param financeBills
	 * @param rptItems
	 */
	@SuppressWarnings("unused")
	private void constructChainAcctFlowRptItems(List<InventoryOrder> purchaseOrders, List<FinanceBillSupplier> financeBills, List<HeadQAccFlowReportItem> rptItems, int clientId, boolean isChain, Date startDate){
		//1. 货品采购单
		if (purchaseOrders !=null && purchaseOrders.size() >0){
			for (InventoryOrder order : purchaseOrders){
				HeadQCust cust = headQCustDaoImpl.get(clientId, true);
				HeadQSupplier supplier  = headQSupplierDaoImpl.get(clientId, true);
				String orderType = "";
				if (isChain)
					orderType = order.getOrder_type_chain();
				else 
					orderType = order.getOrder_type_ws();
				
				String acctFlowType ="";
				if (order.getOrder_type() == InventoryOrder.TYPE_SALES_ORDER_W)
					acctFlowType = HeadQAccFlowReportItem.ACCT_FLOW_TYPE_INCREASE;
				else if (order.getOrder_type() == InventoryOrder.TYPE_SALES_RETURN_ORDER_W)
					acctFlowType = HeadQAccFlowReportItem.ACCT_FLOW_TYPE_DECREASE;
				else if (order.getOrder_type() == InventoryOrder.TYPE_SALES_FREE_ORDER_W)
					acctFlowType = HeadQAccFlowReportItem.ACCT_FLOW_TYPE_NOCHANGE;
				
				HeadQAccFlowReportItem acctFlowItem = new HeadQAccFlowReportItem(supplier, order.getOrder_EndTime(), orderType, ChainAcctFlowReportItem.ITEM_TYPE_PURCHASE,acctFlowType, order.getTotalQuantity(), order.getTotalWholePrice() - order.getTotalDiscount(), order.getOrder_ID(),order.getComment(), 0,0);
				rptItems.add(acctFlowItem);
			}
		}
		
		//2. 财务单据
		if (financeBills != null && financeBills.size() > 0){
			for (FinanceBillSupplier bill : financeBills){
				//HeadQCust cust = bill.getCust();
				String billTypeName = "";
				if (isChain)
					billTypeName = bill.getTypeChainS();
				else 
					billTypeName = bill.getTypeHQS();
				
				int billType = bill.getType();
				String acctFlowType = "";
				double invoiceTotal = bill.getInvoiceTotal();
				if (billType == FinanceBill.FINANCE_INCREASE_HQ || billType == FinanceBill.FINANCE_PAID_HQ) {
					acctFlowType = HeadQAccFlowReportItem.ACCT_FLOW_TYPE_INCREASE;
					invoiceTotal -= bill.getInvoiceDiscount();
				} else if (billType == FinanceBill.FINANCE_DECREASE_HQ || billType == FinanceBill.FINANCE_INCOME_HQ){
					acctFlowType = HeadQAccFlowReportItem.ACCT_FLOW_TYPE_DECREASE;
					invoiceTotal += bill.getInvoiceDiscount();
				}
				
				HeadQAccFlowReportItem acctFlowItem = new HeadQAccFlowReportItem(bill.getSupplier(), bill.getBillDate(), billTypeName, HeadQAccFlowReportItem.ITEM_TYPE_FINANCE, acctFlowType, 0, invoiceTotal, bill.getId(), bill.getComment(), 0,0);
				rptItems.add(acctFlowItem);
			}
		}
		
		//3. 排序一下
		Collections.sort(rptItems, new HeadQAccFlowReportItemSort());
		
		//4. 获取前面的累计欠款
		Date firstDate = null;
		double acctBefore  = 0;
		if (rptItems.size() > 0){
			firstDate = rptItems.get(0).getDate();
		} else 
			firstDate = startDate;
			
		
		if (firstDate != null)
		    acctBefore = supplierAcctFlowDaoImpl.getAccumulateAcctFlowBefore(clientId, firstDate);
		
		//5. 准备数据
		HeadQCust cust = headQCustDaoImpl.get(clientId, true);
		if (cust != null)
		     acctBefore += cust.getInitialAcctBalance();
		
		double acctAfter = acctBefore;
		for (HeadQAccFlowReportItem rptItem: rptItems){
			double thisAmt = rptItem.getAmount();
			String acctFlowType = rptItem.getAcctFlowType();
			
			if (acctFlowType.equalsIgnoreCase(ChainAcctFlowReportItem.ACCT_FLOW_TYPE_INCREASE)){
				acctAfter += thisAmt;
				rptItem.setAcctIncrease(thisAmt);
				rptItem.setPostAcct(acctAfter);
			} else if (acctFlowType.equalsIgnoreCase(ChainAcctFlowReportItem.ACCT_FLOW_TYPE_DECREASE)){
				acctAfter = acctAfter - thisAmt;
				rptItem.setAcctDecrease(thisAmt);
				rptItem.setPostAcct(acctAfter);
			} else {
				rptItem.setPostAcct(acctAfter);
			}

		}
		
		//6.0 设置首
		HeadQAccFlowReportItem firstItem = new HeadQAccFlowReportItem();
		firstItem.setDate(startDate);
		firstItem.setPostAcct(acctBefore);
		rptItems.add(0, firstItem);
	}

}
